﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Collections;
namespace Native
{

    public class CompanyNameAndOrderDate
    {
        public string CompanyName { get; set; }
        public DateTime OrderDate { get; set; }
    }

    class TaskNative
    {

        public static IEnumerable<CompanyNameAndOrderDate> FindAllCustomers(DateTime firstDate, DateTime lastDate, 
            string region)
        {
            NorthwindEntities entities = new NorthwindEntities();
            string sql = "SELECT * FROM Customers as cust " +
                "INNER JOIN Orders as ord ON (ord.CustomerID = cust.CustomerID) " +
                "WHERE ord.OrderDate >= {0} AND ord.OrderDate < {1} " +
                "AND ord.ShipCountry = {2}";

            object[] parameters = {firstDate, lastDate, region};

            IEnumerable<CompanyNameAndOrderDate> customers =
                entities.ExecuteStoreQuery<CompanyNameAndOrderDate>(sql, parameters);
            return customers;
        }

        static void Main(string[] args)
        {
            DateTime firstDate = new DateTime(1997, 1, 1);
            DateTime lastDate = new DateTime(1998, 1, 1);


            IEnumerable<CompanyNameAndOrderDate> customers = FindAllCustomers(firstDate, lastDate, "Canada");
            foreach (var item in customers)
            {
                Console.WriteLine(item.CompanyName+" "+item.OrderDate);
            }
        }
    }
}
